﻿CREATE PROCEDURE [dbo].[proc_Customer_GetEmployeeList_ServiceKF]
(
	@cusName nvarchar(500),
	@UserName nvarchar(50),
	@StartIndex int,
	@EndIndex int
)
As
--begin tran
Begin

Declare @cSql varchar(2000)
set @cSql=''
	if @cusName<>'' 
	set @cSql=' and CustomerName like ''%'+@cusName+'%'' '
set @cSql='
	declare @companyid int,@Days int,@DepartmentId int
	EXEC @companyid=proc_Company_GetID '''+@UserName+''',@companyid--单位ID
	set @DepartmentId = (Select DepartmentId From Employee Where UserName='''+@UserName+''')--部门Id
	Set @Days = (Select ProtectDays From Department Where id=@DepartmentId)--系统保护天数
	;WITH list As (Select ROW_NUMBER() OVER (ORDER BY TrackDate DESC) AS Row,
		Id,
		delFlag,
		CustomerId,
		isnull(CustomerName,''...'') as CustomerName,
		Project,
		ProtectId,
		SourceId,
		IndustryOneId,
		TypeId,
		CreateDate,		
		IsNull(ProtectDate,''1900-01-01'') As ProtectDate,
		IsNull((@Days-datediff(day,Isnull(ProtectDate,GetDate()),GetDate())),0) As Countdown,
		ProtectName
	    --Isnull((select Pizhu from Customer_PiZhu where CustomerId= Customer.CustomerId),'''') as Pizhu ,--批注内容字段2015.08.20
		--Isnull((select CusState from Customer_PiZhu where CustomerId= Customer.CustomerId),0) as CusState --是否有批注字段 
	From Customer Where companyid=@companyid and delflag=0 and ServicePerson = '''+@UserName+''' and  Audit=1 '+@cSql+')

Select *,
isnull((Select Title From Customer_Source Where Id=list.SourceId),''...'') As Source,
		isnull((Select Title From Customer_Industry Where Id=list.IndustryOneId),''...'') As IndustryOne,
		isnull((Select Title From Customer_Type Where Id=list.TypeId),''...'') As Type,
ISNULL((Select Title From Customer_Protect Where Id=list.ProtectId),''...'') As Protect,
isnull((select Ename from employee where employee.username=ProtectName),'''') ProtectNameEName
,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' Order By Row
'
print (@cSql)
exec(@cSql)
--commit tran   
End
RETURN
